package workers;

import conf.AppConfig;
import db.MySQL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import main.Message;
import main.User;

/**
 *
 * @author xDrake
 */
public class Worker_db implements IWorker {
    
    private String host = "";
    private String database = "";
    private String db_user = "";
    private String db_pass = "";

    public Worker_db() {
        AppConfig config = new AppConfig();
        host = config.getSql_server();
        database = config.getSql_database();
        db_user = config.getSql_user();
        db_pass = config.getSql_pass();
    }
    
    @Override
    public Boolean addUser(User user) {
        try {
            //INSERT INTO `Users` (`cLogin`, `cPass`, `cName`, `cEmail`) VALUES ('xDrake', 'somePaSS', 'Антон', 'no-reply@xcoder.org.ua');
            MySQL db = new MySQL(host, database, db_user, db_pass);
            db.Connect();
            
            String query = "INSERT INTO `Users` (`cLogin`, `cPass`, `cEmail`) VALUES (?,?,?)";
            int res = db.queryUpdate(query, new Object[]{user.getLogin(),user.getPass(), user.getEmail()} );
            
            db.Close();

            if (res > 0) {
                return true;    
            }else{
                return false;
            }
            
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public String test() {
        return "";
    }

    @Override
    public Boolean isUserExists(String login) {
        try {
            //SELECT count(Users.cLogin)
            //FROM Users
            //WHERE Users.cLogin = 'xDrake';
            MySQL db = new MySQL(host, database, db_user, db_pass);
            db.Connect();
            
            String query  = "SELECT count(Users.cLogin) as result ";
                   query += "FROM Users ";
                   query += "WHERE Users.cLogin = ? ";
                   
            ResultSet res = db.querySelect(query, new Object[]{login});
            int count = 0;
            while (res.next()) {                
                 count = res.getInt("result");
            }
            db.Close();

            if (count > 0) {
                return true;    
            }else{
                return false;
            }
            
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public Boolean isEmailExists(String email) {
        try{
            MySQL db = new MySQL(host, database, db_user, db_pass);
            db.Connect();
            
            String query  = "SELECT count(Users.cEmail) as result ";
                   query += "FROM Users ";
                   query += "WHERE Users.cEmail = ? ";
                   
            ResultSet res = db.querySelect(query, new Object[]{email});
            int count = 0;
            while (res.next()) {                
                 count = res.getInt("result");
            }
            db.Close();

            if (count > 0) {
                return true;    
            }else{
                return false;
            }
            
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public User doLogin(String login, String pass) {
        try{
            MySQL db = new MySQL(host, database, db_user, db_pass);
            db.Connect();
            
            String query  = "SELECT * ";
                   query += "FROM Users ";
                   query += "WHERE Users.cLogin = ? AND Users.cPass = ?  ";
                   
            ResultSet res = db.querySelect(query, new Object[]{login, pass});
            
            User user = new User();
            while (res.next()) {                
                 user.setID( res.getInt("cID"));
                 user.setLogin( res.getString("cLogin"));
                 user.setName( res.getString("cName"));
                 user.setEmail( res.getString("cEmail"));
                 user.setInfo( res.getString("cInfo"));
                 user.setStatus(String.valueOf( res.getString("cStatusID")));
                 user.setImage( res.getString("cImage"));
                 user.setRegistered( res.getTimestamp("cRegistered"));
            }
            db.Close();

            if (user.getLogin() != null) {
                return user;    
            }else{
                return null;
            }
            
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public Boolean sendMessage(Message msg) {
        try {
            MySQL db = new MySQL(host, database, db_user, db_pass);
            db.Connect();
            
            String query = "INSERT INTO `Messages` (`cUserID`, `cText`) VALUES (?, ?);";
            int res = db.queryUpdate(query, new Object[]{msg.getUserID(), msg.getText()} );

            db.Close();

            if (res > 0) {
                return true;    
            }else{
                return false;
            }
            
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public ArrayList<Message> getLastMessages() {
        try {
            MySQL db = new MySQL(host, database, db_user, db_pass);
            db.Connect();
            
            String query  = "SELECT m.cID as ID, m.cUserID as userID, u.cLogin as login, m.cText as msg, m.cTime as time ";
                   query += "FROM Messages m, Users u ";
                   query += "WHERE m.cUserID = u.cID ";
                   query += "ORDER BY `cTime` ";
                   query += "LIMIT 0, 100";
       
            ResultSet res = db.querySelect(query, new Object[0]);
            
            ArrayList<Message> array = new ArrayList<Message>();
            while (res.next()) {                
                Message m = new Message();
                m.setID( res.getInt("ID") );
                m.setUserID( res.getInt("userID") );
                m.setUserLogin( res.getString("login"));
                m.setText( res.getString("msg"));
                m.setTime( res.getTimestamp("time") );
                array.add(m);
            }
            db.Close();
            return array;        
        //throw new UnsupportedOperationException("Not supported yet.");
        } catch (SQLException ex) {
            return new ArrayList<Message>();
        }
    }
}
